DROP TABLE dept CASCADE CONSTRAINTS;
DROP TABLE emp CASCADE CONSTRAINTS;
DROP TABLE salgrade CASCADE CONSTRAINTS;
purge recyclebin;

CREATE TABLE dept (
    deptno NUMBER(2) NOT NULL,
    dname  VARCHAR2(30),
    loc    VARCHAR2(30)
);
ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY ( deptno );

CREATE TABLE emp (
    empno    NUMBER(4) NOT NULL,
    ename    VARCHAR2(30),
    job      VARCHAR2(30),
    mgr      NUMBER(4),
    hiredate DATE,
    sal      NUMBER(7, 2),
    comm     NUMBER(7, 2),
    deptno   NUMBER(2)
);
ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY ( empno );

CREATE TABLE salgrade (
    grade CHAR(2) NOT NULL,
    losal NUMBER(7, 2),
    hisal NUMBER(7, 2)
);
ALTER TABLE salgrade ADD CONSTRAINT salgrade_pk PRIMARY KEY ( grade );

---------------------------------------------
ALTER TABLE emp
    ADD CONSTRAINT emp_dept_fk FOREIGN KEY ( deptno )
        REFERENCES dept ( deptno );
ALTER TABLE emp
    ADD CONSTRAINT emp_emp_fk FOREIGN KEY ( mgr )
        REFERENCES emp ( empno );